package com.smit.livevideo.sample.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.smit.utils.LogUtil;

/**
 * Created by smfang on 2015/11/24.
 * 数据库工具类，主要执行数据库的插入、删除等操作
 */
public final class SqliteBaseUtil
{
    private static final String TAG = SqliteBaseUtil.class.getSimpleName();
    private static final String ID = "_id";
    private static SqliteBaseUtil instance = null;
    private DatabaseHelper dbHelper = null;

    private SQLiteDatabase db = null;

    /**
     * 私有构造函数
     * @param context context参数
     */
    private SqliteBaseUtil(Context context)
    {
        dbHelper = DatabaseHelper.getInstance(context);
    }

    /**
     * 数据库初始化调用
     * @param context context参数
     */
    public static void init(Context context)
    {
        instance = new SqliteBaseUtil(context);
    }

    /**
     * 获取单例对象
     * @return SqliteBaseUtil
     */
    public static SqliteBaseUtil getInstace()
    {
        return instance;
    }

    /**
     * 获取DatabaseHelper 对象
     * @return DatabaseHelper
     */
    public DatabaseHelper getDbHelper()
    {
        return dbHelper;
    }

    /**
     * 根据表名查询
     *
     * @param tableName 表名
     * @param whereMap 数据库查找的数据源
     * @return List<Map<String, String>>
     */
    /**
     *
     * @param tableName 表名
     * @param whereMap 数据库查找的数据源
     * @param orderBy 所需查找的数据
     * @param isDecrease 查找结果顺序排列还是倒序排列
     * @return List<Map<String, String>>
     */
    public List<Map<String, String>> query(String tableName, Map<String, String> whereMap, String orderBy,
            boolean isDecrease)
    {
        Cursor cursor = null;
        List<Map<String, String>> res = new ArrayList<Map<String, String>>();
        try
        {
            db = dbHelper.getReadableDatabase();
            db.enableWriteAheadLogging();
            String[] columns = null;
            List<Object> list = parseMap(whereMap);
            String whereClause = (String) list.get(0);
            String[] selectionArgs = null;
            if (list.size() > 1)
            {
                selectionArgs = (String[]) list.get(1);
            }
            String orderByStr = null;

            if (!orderBy.equals(""))
            {
                orderByStr = isDecrease ? orderBy + " desc" : orderBy + " asc";
            }

            cursor = db.query(true, tableName, columns, whereClause, selectionArgs, null, null, orderByStr, null);
            while (cursor.moveToNext())
            {
                Map<String, String> m = new HashMap<String, String>();
                for (int i = 0; i < cursor.getColumnCount(); i++)
                {
                    String name = cursor.getColumnName(i);
                    if (cursor.getType(i) == Cursor.FIELD_TYPE_STRING)
                    {
                        m.put(name, cursor.getString(i));
                    }
                    else if (cursor.getType(i) == Cursor.FIELD_TYPE_INTEGER)
                    {
                        m.put(name, cursor.getInt(i) + "");
                    }
                }
                res.add(m);
            }
            cursor.close();
        }
        catch (Exception e)
        {
            LogUtil.trace(TAG, "query Exception!");
        }

        return res;
    }

    /**
     *
     * @param tableName 表名
     * @param orderBy 所需查找的数据
     * @param isDecrease 查找结果顺序排列还是倒序排列
     * @return List<Map<String, String>>
     */
    public List<Map<String, String>> queryAll(String tableName, String orderBy, boolean isDecrease)
    {
        Cursor cursor = null;
        List<Map<String, String>> res = new ArrayList<Map<String, String>>();
        try
        {
            db = dbHelper.getReadableDatabase();
            db.enableWriteAheadLogging();
            String orderByStr = null;
            if (!orderBy.equals(""))
            {
                orderByStr = isDecrease ? orderBy + " desc" : orderBy + " asc";
            }
            cursor = db.query(true, tableName, null, null, null, null, null, orderByStr, null);
            while (cursor.moveToNext())
            {
                Map m = new HashMap();
                for (int i = 0; i < cursor.getColumnCount(); i++)
                {
                    String name = cursor.getColumnName(i);
                    if (cursor.getType(i) == Cursor.FIELD_TYPE_STRING)
                    {
                        m.put(name, cursor.getString(i));
                    }
                    else if (cursor.getType(i) == Cursor.FIELD_TYPE_INTEGER)
                    {
                        m.put(name, cursor.getInt(i) + "");
                    }
                }
                res.add(m);
            }
            cursor.close();
        }
        catch (Exception e)
        {
            LogUtil.trace(TAG, "queryAll Exception!");
        }

        return res;
    }

    /**
     * 插入数据
     * @param tableName 数据库表名
     * @param map 需要插入的数据
     * @return long
     */
    public long insert(String tableName, Map map)
    {
        long num = -1;
        try
        {
            db = dbHelper.getReadableDatabase();
            db.enableWriteAheadLogging();
            ContentValues values = mapTocontentValues(map);
            num = db.insert(tableName, null, values);
        }
        catch (Exception e)
        {
            LogUtil.trace(TAG, "insert(String tableName, Map map) Exception!");
        }

        return num;
    }

    /**
     * 插入数据
     * @param tableName 数据库表名
     * @param values 需要插入的数据
     * @return long
     */
    public long insert(String tableName, ContentValues values)
    {
        long num = -1;
        try
        {
            db = dbHelper.getReadableDatabase();
            db.enableWriteAheadLogging();
            num = db.insert(tableName, null, values);
        }
        catch (Exception e)
        {
            LogUtil.trace(TAG, "insert(String tableName, ContentValues values) Exception!");
        }

        return num;
    }

    /**
     * 更新数据
     * @param tableName 数据库表名
     * @param map 需要更新的数据
     * @param wheremap 需要更新的数据项
     * @return long
     */
    public long update(String tableName, Map map, Map<String, String> wheremap)
    {
        long res = -1;
        try
        {
            db = dbHelper.getReadableDatabase();
            db.enableWriteAheadLogging();
            List<Object> list = parseMap(wheremap);
            String whereClause = (String) list.get(0);
            String[] selectionArgs = null;
            if (list.size() > 1)
            {
                selectionArgs = (String[]) list.get(1);
            }
            ContentValues values = mapTocontentValues(map);
            res = db.update(tableName, values, whereClause, selectionArgs);
        }
        catch (Exception e)
        {
            LogUtil.trace(TAG, "update Exception!");
        }

        return res;
    }

    /**
     * 删除数据
     * @param tableName 表名
     * @param map 需要删除的数据
     * @return long
     */
    public long delete(String tableName, Map<String, String> map)
    {
        int res = -1;
        try
        {
            db = dbHelper.getReadableDatabase();
            db.enableWriteAheadLogging();
            List<Object> list = parseMap(map);
            String whereClause = (String) list.get(0);
            String[] whereArgs = (String[]) list.get(1);
            res = db.delete(tableName, whereClause, whereArgs);
        }
        catch (Exception e)
        {
            LogUtil.trace(TAG, "delete Exception!");
        }
        return res;
    }

    /**
     * 删除数据
     * @param tableName 表名
     * @param id 根据id删除数据
     * @return long
     */
    public long deleteById(String tableName, String id)
    {
        int res = -1;
        try
        {
            db = dbHelper.getReadableDatabase();
            db.enableWriteAheadLogging();
            String whereClause = "_id = ?";
            String[] whereArgs = {id};
            res = db.delete(tableName, whereClause, whereArgs);
        }
        catch (Exception e)
        {
            LogUtil.trace(TAG, "deleteById Exception!");
        }

        return res;
    }

    /**
     * 删除全部数据
     * @param tableName 表名
     * @return long
     */
    public long deleteAll(String tableName)
    {
        int res = -1;
        try
        {
            db = dbHelper.getReadableDatabase();
            db.enableWriteAheadLogging();
            res = db.delete(tableName, "1=1", new String[] {});
        }
        catch (Exception e)
        {
            LogUtil.trace(TAG, "deleteAll Exception!");
        }

        return res;
    }

    /**
     * 关闭数据库
     */
    public void closeDb()
    {
        if (db != null)
        {
            db.close();
        }
    }

    /**
     * 解析 Map
     * @param map 解析的数据
     * @return List<Object>
     */
    private List<Object> parseMap(Map<String, String> map)
    {
        String whereClause = "1=1 ";
        List<Object> list = new ArrayList<Object>();
        String[] selectionArgs = null;
        if (map != null && map.size() > 0)
        {
            selectionArgs = new String[map.size()];
            Set<Entry<String, String>> set = map.entrySet();
            int i = -1;
            for (Entry<String, String> ent : set)
            {
                i = i + 1;
                whereClause += " and " + ent.getKey() + "=?";
                selectionArgs[i] = ent.getValue();
            }
            i = -1;
        }
        list.add(whereClause);
        if (selectionArgs != null)
        {
            list.add(selectionArgs);
        }
        return list;
    }

    /**
     * Map转ContentValues
     * @param map 需要转换的数据
     * @return ContentValues
     */
    public static ContentValues mapTocontentValues(Map map)
    {
        ContentValues values = new ContentValues();
        if (map != null && map.size() > 0)
        {
            Set<Entry<String, Object>> set = map.entrySet();
            for (Entry<String, Object> ent : set)
            {
                Object value = ent.getValue();
                if (value instanceof Integer)
                {
                    values.put(ent.getKey(), (Integer) value);
                }
                else
                {
                    values.put(ent.getKey(), String.valueOf(value));
                }
            }
        }
        return values;
    }
}
